package lxt.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import lxt.dao.ChargeRecordDao;
import lxt.entity.ChargeRecord;

public class ChargeRecordDaoImpl extends ABseDaoImpl implements ChargeRecordDao {

	public List<?> queryResumeForUpdatePre(ChargeRecord chargeRecord) {
		StringBuffer hql = new StringBuffer();
		hql.append("select max(c.oweFee), sum(c.realPayFee) - ?, max(c.oweFee) - sum(c.realPayFee) from ChargeRecord as c ");
		hql.append("where c.clientInfo.clientId = ? and c.invoiceOrder.orderId = ? ");
		hql.append("group by c.clientInfo, c.invoiceOrder ");
		Query query = this.getSession().createQuery(hql.toString());
		query.setParameter(0, chargeRecord.getRealPayFee());
		query.setParameter(1, chargeRecord.getClientInfo().getClientId());
		query.setParameter(2, chargeRecord.getInvoiceOrder().getOrderId());
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> loadAllCharge(String orderId,
			String clientName, String startDate, String endDate,
			Integer offset, Integer length) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.id, b.client_name clientName, a.order_id orderId, a.owe_fee oweFee, ");
		sql.append("a.real_pay_fee realPayFee, a.charge_date chargeDate, ");
		sql.append("a.owe_fee - a.real_pay_fee curretOwe, a.username ");
		sql.append("FROM tb_charge_record a LEFT JOIN tb_client b ON a.client_id = b.client_id ");
		sql.append("WHERE a.real_pay_fee > 0 and b.client_name LIKE ? AND a.order_id LIKE ? ");
		if (startDate != null && !"".equals(startDate)) {
			sql.append(" and a.charge_date >= '" + startDate + "' ");
		}
		if (endDate != null && !"".equals(endDate)) {
			sql.append(" and a.charge_date <= '" + endDate + "' ");
		}
		sql.append("order by a.entry_time desc ");
		if(length != null){
			sql.append("limit " + (offset == null ? 0: offset.intValue()));
			sql.append(", " + length.intValue());
		}

		// 创建SQLquery对象
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		// 设置返回类型为map
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置列类型，避免Column not found
		query.addScalar("id", Hibernate.STRING);
		query.addScalar("clientName", Hibernate.STRING);
		query.addScalar("orderId", Hibernate.STRING);
		query.addScalar("oweFee", Hibernate.DOUBLE);
		query.addScalar("realPayFee", Hibernate.DOUBLE);
		query.addScalar("chargeDate", Hibernate.DATE);
		query.addScalar("curretOwe", Hibernate.DOUBLE);
		query.addScalar("username", Hibernate.STRING);

		query.setParameter(0, clientName == null ? "%" : "%" + clientName + "%");
		query.setParameter(1, orderId == null ? "%" : "%" + orderId + "%");
		
		return query.list();
	}
	
	/**
	 * 查询欠费订单
	 */
	@SuppressWarnings("unchecked")
	public List<?> loadByPageWithClient(String clientId, Date startDate,
			Date endDate, Integer offset, Integer pageSize) {
		ArrayList<Object> param = new ArrayList<Object>();
		StringBuffer hql = new StringBuffer("select c, max(c.oweFee), sum(c.realPayFee)  from ChargeRecord as c ");
		hql.append("where c.clientInfo.clientId = ? ");
		param.add(clientId);
		if(startDate != null){
			hql.append("And c.invoiceOrder.entryTime >= ?");
			param.add(startDate);
		}
		if(endDate != null){
			hql.append("And c.invoiceOrder.entryTime <= ?");
			param.add(endDate);
		}
		hql.append(" group by c.clientInfo, c.invoiceOrder having max(c.oweFee) - sum(c.realPayFee) > 0");
		
		Query query = this.getSession().createQuery(hql.toString());
		for(int i=0;i<param.size();i++){
			query.setParameter(i, param.get(i));
		}
		if(pageSize != null){
			query.setFirstResult(offset.intValue());
			query.setMaxResults(pageSize.intValue());
		}
		return query.list();
	}

	public int countLast(ChargeRecord chargeRecord) {
		StringBuffer hql = new StringBuffer("select c from ChargeRecord as c where c.realPayFee > 0 and c.oweFee < ? ");
		Query query = this.getSession().createQuery(hql.toString());
		query.setParameter(0, chargeRecord.getOweFee());
		return query.list().size();
	}
	/**
	 * 根据订单Id删除数据
	 */
	@SuppressWarnings("unchecked")
	public void deleteOrder(String orderId){
		StringBuffer hql = new StringBuffer(" Select a from ChargeRecord a where a.invoiceOrder.orderId = ? ");
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(orderId);
		List<ChargeRecord> temp = this.getHibernateTemplate().find(hql.toString(),param.toArray());
		for(int i=0;i<temp.size();i++){
			ChargeRecord entity = temp.get(i);
			this.getHibernateTemplate().delete(entity);
		}
	}
	/**
	 * 根据订单id和客户id查询nextChargeDate
	 * @param orderId
	 * @param clientId
	 * @return
	 */
	public ChargeRecord queryByNextChargeDate(String orderId,String clientId){
		StringBuffer hql = new StringBuffer(" Select a from ChargeRecord a where 1=1 ");
		ArrayList<Object> param = new ArrayList<Object>();
		if(orderId!=null&&!orderId.equals("")){
			hql.append(" And a.invoiceOrder.orderId = ? ");
			param.add(orderId);
		}
		if(clientId!=null&&!clientId.equals("")){
			hql.append(" And a.clientInfo.clientId = ? ");
			param.add(clientId);
		}
		hql.append(" order by a.oweFee desc limit 1 ");
		
		if(this.getHibernateTemplate().find(hql.toString(),param.toArray()).size()==0){
			return null;
		}
		return (ChargeRecord) this.getHibernateTemplate().find(hql.toString(),param.toArray()).get(0);
		 
	}
}
